﻿CREATE PROCEDURE [dbo].[proc_Employee_Wage_Create_Count]
	(
		@DepartmentId int,
		@EName nvarchar(50),
		@CreateName nvarchar(50)
	)
AS
	Declare @CompanyId int
	Set @CompanyId = (Select CompanyId From aspnet_users Where UserName=@CreateName)
	
	declare @indextable table(id int identity(1,1),nid int)
	;WITH ChildDepartment(ID,DName,PId) AS
	(
		SELECT ID,DName,ParentID FROM department WHERE id=@departmentid
	    
		UNION ALL
		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
		INNER JOIN department ON d.ID = department.ParentID
	)
	
	insert into @indextable(nid) select Id from ChildDepartment;
	
	Select 
		Count(Id) 
	From
				Employee_Wage
				Where
					(@CompanyId=0 or CompanyId = @CompanyId)
					and FaFangMonth = (select convert(varchar(7),dateadd(mm,-1,getdate()),120) ) 
					and (@Departmentid=0 or Departmentid in(select c.nid from @indextable c))
					and EName like '%'+@EName+'%'
	RETURN
